﻿Imports DocumentFormat.OpenXml.Spreadsheet
Imports FineUI
Imports Kimmo
Imports Microsoft.VisualBasic.CompilerServices
Imports System.Collections
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Diagnostics
Imports System.Runtime.CompilerServices

Public Class clsColumns
    Public Property ID As Integer = -1
    Public Property Position As String = ""
    Public Property ProjectID As Integer = 0
    Public Property ProjectName As String = ""
    Public Property TableID As Integer = 0
    Public Property TableName As String = ""
    Public Property ColumnName As String = ""
    Public Property Caption As String = ""
    Public Property EmptyText As String = ""
    Public Property ToolTip As String = ""
    Public Property DataType As String = "Text"
    Public Property DataLength As String = "255"
    Public Property DefaultValue As String = ""
    Public Property ControlType As String = "TextBox"
    Public Property DataFormat As String = "{0}"
    Public Property ColumnWidth As String = "100"
    Public Property TextAlign As String = "Left"
    Public Property IsPrimary As String = "N"
    Public Property IsNull As String = "N"
    Public Property IsAuto As String = "N"
    Public Property IsOnGrid As String = "Y"
    Public Property IsIndex As String = "N"
    Public Property IsRequired As String = "N"
    Public Property IsSearchable As String = "N"
    Public Property IsGroup As String = "N"
    Public Property IsSum As String = "N"
    Public Property IsReadOnly As String = "N"
    Public Property IsNew As Boolean = False
    Public Property IndexImage As String = "none"
    Public Property IsVisible As String = "Y"
    Public Property IsSortable As String = "N"
    Public Property IsGridToolTip As String = ""
    Public Property DateFields As String = ""
    Public Property IsExpand As String = "N"
    Public Property IsAutoPostBack As String = "N"
    Public Property FieldsToUpdate As String = "ID,IsExpand,IsGridToolTip,IsVisible,IsSortable,IndexImage,Position,ProjectID,ProjectName,TableID,TableName,ColumnName,Caption,EmptyText," & _
        "DataType,DataLength,DefaultValue,ControlType,DataFormat,ColumnWidth,TextAlign,IsPrimary,IsNull,IsAuto,IsOnGrid,IsIndex,IsRequired," & _
        "IsSearchable,IsGroup,IsSum,IsReadOnly,ToolTip,IsAutoPostBack"
    
    Public Shared Function DataTypes() As List(Of String)
        Dim nDT As New List(Of String)
        nDT.Add("Integer")
        nDT.Add("Long")
        nDT.Add("Single")
        nDT.Add("Double")
        nDT.Add("Date")
        nDT.Add("Text")
        nDT.Add("LongText")
        nDT.Add("YesNo")
        nDT.Add("Image")
        Return nDT
    End Function

    Public Shared Sub FixPositions(ByVal strTableID As String)
        Dim xTable As DataTable = SQLCompact.OpenDataTable("Select ID,Position from Columns where TableID = " & Val(strTableID) & " order by Position;")
        SQLCompact.StoredQueriesInit()
        Dim pCnt As Integer = 0
        Dim pStr As String = ""
        For Each xRow As DataRow In xTable.Rows
            pCnt = pCnt + 1
            pStr = clsDL.StrFormat(pCnt.ToString, "R02")
            Dim sQry As String = "update [Columns] set [Position] = '" & pStr & "' where ID = " & xRow.Item("ID").ToString
            SQLCompact.StoredQueriesAdd(sQry)
        Next
        SQLCompact.StoredQueriesExec()
    End Sub

    Public Shared Sub UpdateTableName(ByVal strTableID As String, ByVal strTableName As String)
        SQLCompact.RunQuery("update [Columns] set [TableName] = '" & strTableName & "' where TableID = " & strTableID)
    End Sub

    Public Shared Sub UpdateProjectName(ByVal strTableID As String, ByVal strTableName As String)
        SQLCompact.RunQuery("update [Columns] set [ProjectName] = '" & strTableName & "' where ProjectID = " & strTableID)
    End Sub

    Public Shared Function TextAligns() As List(Of String)
        Dim nTA As New List(Of String)
        nTA.Add("Left")
        nTA.Add("Center")
        nTA.Add("Right")
        Return nTA
    End Function

    Public Shared Function DataFormats() As List(Of String)
        Dim nDF As New List(Of String)
        nDF.Add("{0}")
        nDF.Add("{0:dd/MM/yyyy}")
        nDF.Add("{0:MM/dd/yyyy}")
        nDF.Add("{0:yyyy-MM-dd}")
        nDF.Add("{0:#,##0.00}")
        nDF.Add("{0:#,##0}")
        nDF.Add("{0:yyyy-MMM}")
        nDF.Add("~/images/{0}.gif")
        Return nDF
    End Function

    Public Shared Function ControlTypes() As List(Of String)
        Dim nCT As New List(Of String)
        nCT.Add("Image")
        nCT.Add("HyperLink")
        nCT.Add("HtmlEditor")
        nCT.Add("CheckBox")
        nCT.Add("TextBox")
        nCT.Add("Label")
        nCT.Add("ImageText")
        nCT.Add("TextImage")
        nCT.Add("RadioButton")
        nCT.Add("FileUpload")
        'nCT.Add("LinkButton")
        nCT.Add("DropDownList")
        nCT.Add("DatePicker")
        nCT.Add("HiddenField")
        nCT.Add("NumberBox")
        nCT.Add("TextArea")
        nCT.Add("TimePicker")
        nCT.Add("TriggerBox")
        nCT.Add("TwinTriggerBox")
        'nCT.Add("Accordion")
        'nCT.Add("ContentPanel")
        'nCT.Add("Form")
        'nCT.Add("GroupPanel")
        'nCT.Add("Panel")
        'nCT.Add("SimpleForm")
        'nCT.Add("Tree")
        'nCT.Add("Window")
        'nCT.Add("Grid")
        'nCT.Add("RegionPanel")
        'nCT.Add("TabStrip")
        Return nCT
    End Function

    Public Sub New()
        MyBase.New()
        ClearValues()
        IsNew = False
        DateFields = ""
        FieldsToUpdate = "ID,IsExpand,IsGridToolTip,IsVisible,IsSortable,IndexImage,Position,ProjectID,ProjectName,TableID,TableName,ColumnName,Caption,EmptyText," & _
   "DataType,DataLength,DefaultValue,ControlType,DataFormat,ColumnWidth,TextAlign,IsPrimary,IsNull,IsAuto,IsOnGrid,IsIndex,IsRequired," & _
   "IsSearchable,IsGroup,IsSum,IsReadOnly,ToolTip,IsAutoPostBack"

    End Sub

    Public Sub ClearValues()
        ID = -1
        Position = ""
        ProjectID = 0
        ProjectName = ""
        TableID = 0
        TableName = ""
        ColumnName = ""
        Caption = ""
        EmptyText = ""
        ToolTip = ""
        DataType = ""
        DataLength = ""
        DefaultValue = ""
        ControlType = ""
        DataFormat = ""
        ColumnWidth = ""
        TextAlign = ""
        IsPrimary = ""
        IsNull = ""
        IsAuto = ""
        IsOnGrid = ""
        IsIndex = ""
        IsRequired = ""
        IsSearchable = ""
        IsGroup = ""
        IsSum = ""
        IsReadOnly = ""
        IndexImage = "none"
        IsVisible = "Y"
        IsGridToolTip = "N"
        IsSortable = "N"
        IsExpand = "N"
        IsAutoPostBack = "N"
    End Sub

    Public Shared Sub DeleteByID(ByVal StrID As String)
        SQLCompact.RunQuery("delete from [Columns] where ID = " & Val(StrID), True)
    End Sub

    Public Shared Sub Columns_Blank(ByVal strTableID As String)
        Dim clsA As New clsColumns
        Dim clsT As New clsTables
        clsT.Read(strTableID)
        clsA.ID = -1
        clsA.IsNew = True
        clsA.ProjectID = clsT.ProjectID
        clsA.ProjectName = clsT.ProjectName
        clsA.TableID = Val(strTableID)
        clsA.TableName = clsT.TableName
        clsA.ColumnName = "Column"
        clsA.DataType = "Text"
        clsA.DataLength = "100"
        clsA.ControlType = "TextBox"
        clsA.DataFormat = "{0}"
        clsA.ColumnWidth = "100"
        clsA.TextAlign = "Left"
        clsA.IsPrimary = "N"
        clsA.IsNull = "Y"
        clsA.IsAuto = "N"
        clsA.IsOnGrid = "Y"
        clsA.IsIndex = "Y"
        clsA.IsRequired = "Y"
        clsA.IsSearchable = "Y"
        clsA.IsGroup = "N"
        clsA.IsSum = "N"
        clsA.IsReadOnly = "N"
        clsA.IndexImage = "none"
        clsA.IsVisible = "Y"
        clsA.IsSortable = "N"
        clsA.IsGridToolTip = "N"
        clsA.IsExpand = "N"
        clsA.IsAutoPostBack = "N"
        clsA.Save()
        clsA.ColumnName = "Column " & clsA.ID.ToString
        clsA.Caption = clsA.ColumnName
        clsA.EmptyText = "Enter " & clsA.Caption & " here."
        clsA.ToolTip = clsA.ColumnName
        clsA.Position = clsA.ID.ToString
        clsA.Position = clsDL.StrFormat(clsA.Position, "R02")
        clsA.Save()
        clsDL.SaveSetting("tableid", clsA.TableID)
        clsDL.SaveSetting("view", "columns")
    End Sub

    Public Sub Read(ByVal sID As String)
        Try
            Dim xset As New DataSet()
            xset = SQLCompact.RecordSeek("Columns", "ID", sID, "*", "Long")
            Dim xcount As Boolean = xset.Tables(0).Rows.Count - 1 <> -1
            If xcount Then
                Dim row As DataRow = xset.Tables(0).Rows(0)
                ID = Val(row("ID").ToString)
                Position = row("Position").ToString
                ProjectID = Val(row("ProjectID").ToString)
                ProjectName = row("ProjectName").ToString
                TableID = Val(row("TableID").ToString)
                TableName = row("TableName").ToString
                ColumnName = row("ColumnName").ToString
                Caption = row("Caption").ToString
                EmptyText = row("EmptyText").ToString
                ToolTip = row("ToolTip").ToString
                DataType = row("DataType").ToString
                DataLength = row("DataLength").ToString
                DefaultValue = row("DefaultValue").ToString
                ControlType = row("ControlType").ToString
                DataFormat = row("DataFormat").ToString
                ColumnWidth = row("ColumnWidth").ToString
                TextAlign = row("TextAlign").ToString
                IsPrimary = row("IsPrimary").ToString
                IsNull = row("IsNull").ToString
                IsAuto = row("IsAuto").ToString
                IsOnGrid = row("IsOnGrid").ToString
                IsIndex = row("IsIndex").ToString
                IsRequired = row("IsRequired").ToString
                IsSearchable = row("IsSearchable").ToString
                IsGroup = row("IsGroup").ToString
                IsSum = row("IsSum").ToString
                IsReadOnly = row("IsReadOnly").ToString
                IndexImage = row("IndexImage").ToString
                IsVisible = row("IsVisible").ToString
                IsSortable = row("IsSortable").ToString
                IsGridToolTip = row("IsGridToolTip").ToString
                IsExpand = row("IsExpand").ToString
                IsAutoPostBack = row("IsAutoPostBack").ToString
                Me.IsNew = False
                xset.Dispose()
            Else
                ClearValues()
                Me.IsNew = True
            End If
        Catch exception1 As Exception
            Throw New Exception(exception1.Message)
        End Try
    End Sub

    Public Sub Save()
        Dim strCommand As String = ""
        Try
            Dim tableName As String = "Columns"
            Dim fldName As String = "ID"
            If Me.ID = -1 Then
                Me.ID = Val(SQLCompact.TableMaxValue(tableName, fldName, "")) + 1
                Me.IsNew = True
            Else
                Me.IsNew = False
            End If
            Me.IndexImage = "none"
            If Me.IsIndex = "Y" Then Me.IndexImage = "index"
            If Me.IsPrimary = "Y" Then Me.IndexImage = "reset"

            If Me.FieldsToUpdate = "*" Then Me.FieldsToUpdate = SQLCompact.Table_FieldNames(tableName, False, ",")
            Me.FieldsToUpdate = clsDL.MvRemoveDuplicates(Me.FieldsToUpdate, ",")
            Dim str2 As String = clsDL.MvPrefixBy(Me.FieldsToUpdate, "@", ",")
            If Me.IsNew = True Then
                strCommand = clsDL.BuildInsertCommand(tableName, Me.FieldsToUpdate, ",")
            Else
                strCommand = clsDL.BuildUpdateCommand(tableName, Me.FieldsToUpdate, fldName, ",")
            End If
            Dim spFlds() As String = {}
            Dim command As SqlCeCommand = SQLCompact.OpenCommand(strCommand)
            Dim totFlds As Integer = clsDL.StrParse(spFlds, Me.FieldsToUpdate, ",")
            command.Parameters.Clear()
            For fldCnt As Integer = 1 To totFlds
                Dim strSearch As String = spFlds(fldCnt)
                Dim d As Boolean = clsDL.MvSearch(Me.DateFields, strSearch, ",") > 0
                If Not d Then
                    command.Parameters.AddWithValue(String.Concat("@", strSearch), RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(clsDL.PropertyGet(Me, strSearch)))))
                Else
                    Dim flag As Boolean = Strings.Len(strSearch) = 0
                    If Not flag Then
                        command.Parameters.AddWithValue(String.Concat("@", strSearch), RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(Interaction.IIf(Information.IsDate(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(clsDL.PropertyGet(Me, strSearch))))), Conversions.ToDate(RuntimeHelpers.GetObjectValue(clsDL.PropertyGet(Me, strSearch))), DBNull.Value)))))
                    Else
                        command.Parameters.AddWithValue(String.Concat("@", strSearch), DBNull.Value)
                    End If
                End If
            Next
            command.ExecuteNonQuery()
            SQLCompact.Connection.Close()
            command.Dispose()
        Catch exception1 As Exception
            Throw New Exception(exception1.Message)
        End Try
    End Sub

    Public Shared Sub ShowColumns(ByRef grdViews As FineUI.Grid, ByVal strFilter As String)
        ' init values
        clsFineUI.Grid.Clear(grdViews, "", "")
        clsFineUI.Grid.TableName = "Columns"
        clsFineUI.Grid.DataKeyNames = "ID"
        clsFineUI.Grid.PageSize = 50
        clsFineUI.Grid.AllowPaging = True
        clsFineUI.Grid.AllowSorting = True
        clsFineUI.Grid.LoadMask = False
        clsFineUI.Grid.Title = "Columns"
        clsFineUI.Grid.ShowBorder = False
        clsFineUI.Grid.ShowHeader = False
        clsFineUI.Grid.EnableRowNumber = False
        clsFineUI.Grid.AutoHeight = True
        clsFineUI.Grid.AutoPostBack = False
        clsFineUI.Grid.EnableCheckBoxSelect = True
        clsFineUI.Grid.AutoWidth = True
        clsFineUI.Grid.EnableAlternateRowColor = True
        clsFineUI.Grid.EnableCollapse = True
        clsFineUI.Grid.EnableMouseOverColor = True
        clsFineUI.Grid.EnableMultiSelect = True
        clsFineUI.Grid.EnableViewState = True
        clsFineUI.Grid.EnableAjax = True
        clsFineUI.Grid.ColumnLines = True
        clsFineUI.Grid.ToolTipField = "ColumnName"
        clsFineUI.Grid.IsDatabasePaging = False
        clsFineUI.Grid.ShowGridHeader = True
        clsFineUI.Grid.EnableHeaderMenu = True
        clsFineUI.Grid.EnableColumnHide = True
        clsFineUI.Grid.AddImageText("IndexImage", "ID", "ID", "~/images/{0}.gif", "", "50", FineUI.TextAlign.Left, False)
        clsFineUI.Grid.AddTextBox("Position", "Position", "{0}", "", "60", FineUI.TextAlign.Left, False)
        clsFineUI.Grid.AddTextBox("ColumnName", "Name", "{0}", "", "160", FineUI.TextAlign.Left, False)
        clsFineUI.Grid.AddWizard()
        clsFineUI.Grid.AddComboBox("DataType", "Data Type", "{0}", "", "160", FineUI.TextAlign.Left, False, DataTypes)
        clsFineUI.Grid.AddTextBox("Caption", "Caption", "{0}", "", "160", FineUI.TextAlign.Left, False)
        clsFineUI.Grid.AddTextBox("EmptyText", "Empty Text", "{0}", "", "160", FineUI.TextAlign.Left, False)
        clsFineUI.Grid.AddTextBox("ToolTip", "Tooltip", "{0}", "", "160", FineUI.TextAlign.Left, False)
        clsFineUI.Grid.AddTextBox("DataLength", "Data Length", "{0}", "", "90", FineUI.TextAlign.Left, False)
        clsFineUI.Grid.AddTextBox("DefaultValue", "Default Value", "{0}", "", "160", FineUI.TextAlign.Left, False)
        clsFineUI.Grid.AddComboBox("ControlType", "Control Type", "{0}", "", "110", FineUI.TextAlign.Left, False, ControlTypes)
        clsFineUI.Grid.AddComboBox("DataFormat", "Data Format", "{0}", "", "160", FineUI.TextAlign.Left, False, DataFormats)
        clsFineUI.Grid.AddTextBox("ColumnWidth", "Column Width", "{0}", "", "110", FineUI.TextAlign.Left, False)
        clsFineUI.Grid.AddComboBox("TextAlign", "Text Align", "{0}", "", "110", FineUI.TextAlign.Left, False, TextAligns)
        clsFineUI.Grid.AddCheckBoxWeb("IsPrimary", "Primary", , "50")
        clsFineUI.Grid.AddCheckBoxWeb("IsVisible", "Visible", , "50")
        clsFineUI.Grid.AddCheckBoxWeb("IsSortable", "Sortable", , "50")
        clsFineUI.Grid.AddCheckBoxWeb("IsGridToolTip", "ToolTipField", , "70")
        clsFineUI.Grid.AddCheckBoxWeb("IsExpand", "Expand", "50")
        clsFineUI.Grid.AddCheckBoxWeb("IsNull", "Null", , "50")
        clsFineUI.Grid.AddCheckBoxWeb("IsAuto", "Auto", , "50")
        clsFineUI.Grid.AddCheckBoxWeb("IsOnGrid", "On Grid", , "60")
        clsFineUI.Grid.AddCheckBoxWeb("IsIndex", "Index", , "50")
        clsFineUI.Grid.AddCheckBoxWeb("IsRequired", "Required", , "60")
        clsFineUI.Grid.AddCheckBoxWeb("IsSearchable", "Searchable", , "70")
        clsFineUI.Grid.AddCheckBoxWeb("IsGroup", "Group", , "50")
        clsFineUI.Grid.AddCheckBoxWeb("IsSum", "Sum", , "50")
        clsFineUI.Grid.AddCheckBoxWeb("IsReadOnly", "Read Only", , "60")
        clsFineUI.Grid.AddCheckBoxWeb("IsAutoPostBack", "Auto Post Back", , "100")
        clsFineUI.Grid.AddSave()
        clsFineUI.Grid.AddClone()
        clsFineUI.Grid.AddDelete()
        If Len(strFilter) > 0 Then
            clsFineUI.Grid.Filter = strFilter
        End If
        clsFineUI.Grid.OrderBy = "ID"
        clsFineUI.Grid.BuildSelectCommand()
        clsFineUI.Grid.UseSQLCompact = True
        clsFineUI.Grid.Update(Nothing)
    End Sub

    Public Shared Function TableIDByID(ByVal ID As String) As String
        Dim mv As String = SQLCompact.RecordReadToMv("Columns", "ID", ID.ToString(), "TableID", ";", "Long")
        Return mv
    End Function

    Public Shared Function ToolTipField(ByVal TableID As String) As String
        Dim sQRY As String = "select ColumnName from Columns where TableID = " & TableID & " and IsGridToolTip = 'Y';"
        Dim mv As String = SQLCompact.TableDistinct(sQRY, "ColumnName", ";")
        Return clsDL.MvField(mv, 1, ";")
    End Function

    Public Shared Function PrimaryKey(ByVal TableID As String) As String
        Dim sQRY As String = "select ColumnName from Columns where TableID = " & TableID & " and IsPrimary = 'Y';"
        Dim mv As String = SQLCompact.TableDistinct(sQRY, "ColumnName", ";")
        Return clsDL.MvField(mv, 1, ";")
    End Function

    Public Shared Function ProjectNameByTableID(ByVal ID As String) As String
        Dim mv As String = SQLCompact.RecordReadToMv("Columns", "TableID", ID.ToString(), "ProjectName", ";", "Long")
        Return clsDL.MvField(mv, 1, ";")
    End Function

    Public Shared Function TableNameByTableID(ByVal ID As String) As String
        Dim mv As String = SQLCompact.RecordReadToMv("Columns", "TableID", ID.ToString(), "TableName", ";", "Long")
        Return clsDL.MvField(mv, 1, ";")
    End Function

    Public Shared Function ProjectIDByID(ByVal ID As String) As String
        Dim mv As String = SQLCompact.RecordReadToMv("Columns", "ID", ID.ToString(), "ProjectID", ";", "Long")
        Return mv
    End Function

    
End Class
